from flask import Flask, render_template, request, url_for, jsonify
from datetime import timedelta
from dbkit import Database
import math

app = Flask(__name__)
app.config['SEND_FILE_MAX_AGE_DEFAULT'] = timedelta(seconds=1)
app.jinja_env.auto_reload = True

dbName = 'company.db'
dbkit = Database()
dbkit.key("企业名称")


@app.route('/', methods=['GET'])
def page_index():
    return render_template('index.html')


@app.route('/login', methods=['POST'])
def page_login():
    if request.method == 'POST':
        print("signalLogin received!")
        data = request.get_json()
        print(data)
        username = data[0]
        password = data[1]
        dbkit.open(dbName)
        dbkit.key("用户名")
        account = dbkit.getCells("账户", expr="[用户名]='" + username + "'")
        if len(account) == 0:
            data = "Invalid"
        else:
            account = account[0]
            if account[1] == password:
                if account[2]:
                    data = "Admin"
                else:
                    data = "Success"
            else:
                data = "Wrong"
        dbkit.close()
        print("slotLogin triggering...")
        print(data)
        return data


@app.route('/register', methods=['POST'])
def page_register():
    if request.method == 'POST':
        print("signalRegister received!")
        data = request.get_json()
        print(data)
        username = data[0]
        password = data[1]
        dbkit.open(dbName)
        dbkit.key("用户名")
        account = dbkit.getCells("账户", expr="[用户名]='" + username + "'")
        if len(account) == 0:
            dbkit.addRow("账户", username)
            dbkit.fillCell("账户", username, "密码", password)
            dbkit.db.commit()
            account = dbkit.getCells("账户", expr="[用户名]='" + username + "'")
            if len(account) > 1:
                data = "Success"
            else:
                data = "Failure"
        else:
            data = "Conflict"
        dbkit.close()
        print("slotRegister triggering...")
        print(data)
        return data


@app.route('/filter', methods=['GET', 'POST'])
def page_filter():
    if request.method == 'GET':
        return render_template('filter.html')
    elif request.method == 'POST':
        print("signalFilter received!")
        data = request.get_json()
        print(data)
        amount = data["amount"]
        page = data["page"]
        sortby = "order by [%s]" % data["sortby"]
        constraint = {}
        for field in data["constraint"]:
            if len(data["constraint"][field]) > 0:
                constraint[field] = str(data["constraint"][field])
        if len(constraint) == 0:
            constraint = ""
        else:
            constraint = " and ".join(["[%s]='%s'" % (field, constraint[field]) for field in constraint])
            constraint = "where " + constraint
        data = {"data": [], "source": [], "current": 1, "total": 1}
        dbkit.open(dbName)
        sqlCount = "select count([企业名称]) from [聚类结果] " + constraint
        dbkit.cursor.execute(sqlCount)
        cnt = int(dbkit.cursor.fetchall()[0][0])
        nPage = math.ceil(cnt / amount)
        data["total"] = nPage
        if page is None:
            page = 1
        if page > nPage:
            page = nPage
        data["current"] = page
        expr = "[企业名称]='%s'"
        if cnt > 0:
            sqlFetch = "select * from [聚类结果] " + constraint \
                       + " " + sortby + " limit %d offset %d" % (amount, amount * (page - 1))
            dbkit.cursor.execute(sqlFetch)
            rows = dbkit.cursor.fetchall()
            for i in range(len(rows)):
                row = rows[i]
                data["data"].append([amount * (page - 1) + i + 1] + list(row))
                data["source"].append(list(dbkit.getCells("非空字段数", expr=expr % row[0])[0][1:]))
        dbkit.close()
        print("slotFilter triggering...")
        print(data)
        return jsonify(data)


@app.route('/query', methods=['GET', 'POST'])
def page_query():
    if request.method == 'GET':
        return render_template('Query.html')
    elif request.method == 'POST':
        print("signalQuery received!")
        data = request.get_json()
        print(data)
        IDs = data["entname"]
        amount = data["amount"]
        page = data["page"]
        data = {"data": [], "source": [], "current": 1, "total": 1}
        dbkit.open(dbName)
        dbkit.getCells("聚类结果", expr='[企业名称]="' + '"')
        expr = "[企业名称]='%s'"
        cnt = 0
        for ID in IDs:
            rows = dbkit.getCells("聚类结果", expr=expr % ID)
            if len(rows) > 0:
                data["data"].append([cnt + 1] + list(rows[0]))
                data["source"].append(list(dbkit.getCells("非空字段数", expr=expr % ID)[0][1:]))
                cnt += 1
        nPage = math.ceil(cnt / amount)
        data["total"] = nPage
        if page is None:
            page = 1
        if page > nPage:
            page = nPage
        data["current"] = page
        if len(data["data"]) > 0:
            data["data"] = [data["data"][i] for i in range(amount * (page - 1), min(amount * page, cnt))]
        dbkit.close()
        print("slotQuery triggering...")
        print(data)
        return jsonify(data)


@app.route('/train', methods=['GET', 'POST'])
def page_train():
    if request.method == 'GET':
        return render_template('admin.html')
    elif request.method == 'POST':
        print("signalTrain received!")
        data = request.get_json()
        print(data)
        data = {}
        dbkit.open(dbName)

        dbkit.train()
        dbkit.metastore("company.checkpoint")
        dbkit.executeScript("company.script")

        cells = dbkit.getCells('评估')
        for row in cells:
            data[row[0]] = list(row[1:])
        dbkit.close()
        print("slotTrain triggering...")
        print(data)
        return jsonify(data)


@app.route('/predict', methods=['GET', 'POST'])
def page_predict():
    if request.method == 'GET':
        return render_template('prediction.html')
    elif request.method == 'POST':
        print("signalPredict received!")
        data = request.get_json()
        print(data)
        d = {}
        for k in data:
            if data[k] != '':
                d[k] = data[k]
        dbkit.open(dbName)
        ID = 'predict'
        table = ""
        state = "在营"
        if "波动经历" in d:
            state = d["波动经历"]
        elif "企业吊销时间" in d:
            if "企业注销时间" in d:
                state = "吊销后注销"
            else:
                state = "吊销"
        elif "企业注销时间" in d:
            state = "注销"

        table = "聚类结果_predict"
        dbkit.copyTable(table, table[:-8])

        table = "抽查通过率等级（聚类：F、E、D、C、B、A）_predict"
        dbkit.copyTable(table, table[:-8])
        if "抽查通过率" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, 'passpercent', float(d["抽查通过率"]))
        table = "波动等级（聚类：A1、B1、C1、D1、E1、F1）_predict"
        dbkit.copyTable(table, table[:-8])
        if state == "注销" and "企业注销时间" in d and "企业创建时间" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "存续时间", int(d["企业注销时间"]) - int(d["企业创建时间"]))
        table = "波动等级（聚类：A2、B2、C2、D2、E2、F2）_predict"
        dbkit.copyTable(table, table[:-8])
        if state == "吊销" and "企业吊销时间" in d and "企业创建时间" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "存续时间", int(d["企业吊销销时间"]) - int(d["企业创建时间"]))
        table = "波动等级（聚类：A3、B3、C3、D3、E3、F3）_predict"
        dbkit.copyTable(table, table[:-8])
        if state == "吊销后注销" and "企业吊销时间" in d and "企业创建时间" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "存续时间", int(d["企业吊销销时间"]) - int(d["企业创建时间"]))
        table = "经营年限（聚类：E、D、C、B、A）_predict"
        dbkit.copyTable(table, table[:-8])
        if "营业执照失效时间" in d and "营业执照生效时间" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "存续时间", int(d["营业执照失效时间"]) - int(d["营业执照生效时间"]))
        table = "参保等级（聚类：C、B、A）_predict"
        dbkit.copyTable(table, table[:-8])
        if "参保种类数目" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "xzbz", int(d["参保种类数目"]))
        table = "执行标的等级（聚类：E、D、C、B、A）_predict"
        dbkit.copyTable(table, table[:-8])
        if "执行标的数目" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "enforce_amount", int(d["执行标的数目"]))
        table = "吊销等级（聚类：A2、B2、C2、D2、E2、F2）_predict"
        dbkit.copyTable(table, table[:-8])
        if state == "吊销" and "企业吊销时间" in d and "企业创建时间" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "存续时间", int(d["企业吊销销时间"]) - int(d["企业创建时间"]))
        table = "吊销等级（聚类：A3、B3、C3、D3、E3、F3）_predict"
        dbkit.copyTable(table, table[:-8])
        if state == "吊销后注销" and "企业吊销时间" in d and "企业创建时间" in d:
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "存续时间", int(d["企业吊销销时间"]) - int(d["企业创建时间"]))
        table = "企业商标等级（待合并）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["驰名商标", "著名商标"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, d[field])
        table = "企业技术水平（待合并）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["专精特新中小企业", "企业技术中小级别"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, d[field])
        table = "企业类型门类（待合并）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["企业(机构)类型", "行业门类", "企业类别"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, d[field])
        table = "企业负面状态（待合并）_predict"
        dbkit.copyTable(table, table[:-8])
        dbkit.addRow(table, ID)
        if state in ["注销", "吊销后注销"]:
            dbkit.fillCell(table, ID, "目前状态", "注销企业")
        elif state == "吊销":
            dbkit.fillCell(table, ID, "目前状态", "吊销企业")
        else:
            dbkit.fillCell(table, ID, "目前状态", "在营企业")
        table = "企业信用-信用水平（权重：信用水平）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["守合同重信用企业", "信用等级"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, d[field])
        table = "企业信用-信用危机（聚类：一般、较大、大、极大）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["行政处罚次数", "黑名单次数", "累计欠税额"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, float(d[field]))
        table = "企业稳定性-企业能力（聚类：差、较差、中等、较强、极强）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["对外投资次数", "中标次数", "商标申请次数", "软件著作权登记次数", "专利申请次数", "域名的知识产权数", "累计认缴额"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, float(d[field]))
        if "累计实缴额" in d and "累计认缴额" in d and 0 < float(d["累计认缴额"]) <= float(d["累计实缴额"]):
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "累计实缴百分比", float(d["累计认缴额"]) / float(d["累计实缴额"]))
        table = "企业规模背景-企业年龄（聚类：中等、较长、长、极长）_predict"
        dbkit.copyTable(table, table[:-8])
        if "企业创建时间" in d:
            t0 = int(d["企业创建时间"])
            t1 = int(d["企业吊销时间"]) if "企业吊销时间" in d else math.inf
            t2 = int(d["企业注销时间"]) if "企业注销时间" in d else math.inf
            t3 = 1576204500000
            dt = min([t1, t2, t3]) - t0
            if dt < 0:
                dt = 0
            dbkit.addRow(table, ID)
            dbkit.fillCell(table, ID, "存续时间", dt)
        table = "企业规模背景-企业规模（聚类：微小、较小、小、中等、较大）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["注册资本", "从业人数", "分支机构数", "网店个数", "招聘记录总条数"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, float(d[field]))
        table = "企业风险-司法风险（聚类：一般、较大、大、极大）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["行政处罚次数", "黑名单次数"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, float(d[field]))
        table = "企业风险-经营风险（聚类：一般、较大、大、极大）_predict"
        dbkit.copyTable(table, table[:-8])
        fields = ["行政处罚次数", "累计欠税额", "异常次数", "出质股权次数", "变更次数"]
        for field in fields:
            if field in d:
                dbkit.addRow(table, ID)
                dbkit.fillCell(table, ID, field, float(d[field]))
        dbkit.db.commit()

        dbkit.predict()
        dbkit.metastore("company.checkpoint")
        dbkit.executeScript("company.script")
        data = dbkit.getCells("聚类结果_predict")[0][1:]
        dbkit.close()
        print("slotPredict triggering...")
        print(data)
        return jsonify(data)


if __name__ == '__main__':
    app.run()
